github.com/readium/readium-lcp-server@v0.0.0-20240509124024-799e77a0bbd6/dbmodel/postgres _db_setup_frontend.sql (about)

     1  CREATE SEQUENCE publication_seq;
     2  
     3  CREATE TABLE publication (
     4      id int PRIMARY KEY DEFAULT NEXTVAL ('publication_seq'),
     5      uuid varchar(255) NOT NULL,	/* == content id */
     6      title varchar(255) NOT NULL,
     7      status varchar(255) NOT NULL
     8  );
     9  
    10  CREATE INDEX uuid_index ON publication (uuid);
    11  
    12  CREATE SEQUENCE user_seq;
    13  
    14  CREATE TABLE "user" (
    15      id int PRIMARY KEY DEFAULT NEXTVAL ('user_seq'),
    16      uuid varchar(255) NOT NULL,
    17      name varchar(64) NOT NULL,
    18      email varchar(64) NOT NULL,
    19      password varchar(64) NOT NULL,
    20      hint varchar(64) NOT NULL
    21  );
    22  
    23  CREATE SEQUENCE purchase_seq;
    24  
    25  CREATE TABLE purchase (
    26      id int PRIMARY KEY DEFAULT NEXTVAL ('purchase_seq'),
    27      uuid varchar(255) NOT NULL,
    28      publication_id int NOT NULL,
    29      user_id int NOT NULL,
    30      license_uuid varchar(255) NULL,
    31      type varchar(32) NOT NULL,
    32      transaction_date timestamp(0),
    33      start_date timestamp(0),
    34      end_date timestamp(0),
    35      status varchar(255) NOT NULL,
    36      FOREIGN KEY (publication_id) REFERENCES publication (id),
    37      FOREIGN KEY (user_id) REFERENCES "user" (id)
    38  );
    39  
    40  CREATE INDEX idx_purchase ON purchase (license_uuid);
    41  
    42  CREATE SEQUENCE license_view_seq;
    43  
    44  CREATE TABLE license_view (
    45      id int PRIMARY KEY DEFAULT NEXTVAL ('license_view_seq'),
    46      uuid varchar(255) NOT NULL,
    47      device_count int NOT NULL,
    48      status varchar(255) NOT NULL,
    49      message varchar(255) NOT NULL
    50  );